Following the announcement that Microsoft is releasing a public preview of Python in Excel, we look at what this will mean for Excel users and how it could help businesses.
What Is Python?
The initial version was created in the late 1980s by Guido van Rossum, with its first official release, Python 0.9.0, coming out in February 1991. It was named after the eponymous Monty Python Show, after having been developed as a successor to the ABC language and was intended to be easy to read and allow for concise code, among other goals.
It’s regarded as a good general-purpose programming language that’s relatively easy to learn due to its simple and straightforward syntax. Python is often used in creating web applications and artificial intelligence applications, and it is the language behind platforms like Pinterest and Instagram.
Added To Excel
Last week, Microsoft announced that is releasing a Public Preview of Python in Excel, thereby enabling the combination of Python and Excel analytics within the same workbook, with no setup required. Microsoft says: “With Python in Excel, you can type Python directly into a cell, the Python calculations run in the Microsoft Cloud, and your results are returned to the worksheet, including plots and visualisations.” In short, this means that Excel users will be able to carry out advanced data analysis in the familiar Excel environment, by accessing Python from the Excel ribbon.
Two other key benefits of the integration highlighted by Microsoft are that it runs securely on the Microsoft Cloud, thereby keeping data private, and it is built to work with Teams. This enables colleagues to (seamlessly) interact with and refresh Python in Excel based analytics without needing to worry about installing additional tools, Python runtimes, or managing libraries and dependencies.
What Sort Of Things Can Be Done With The Excel/Python Combination?
Python’s ability to manipulate Excel tables will be of particular help to businesses that frequently work with data because it offers many practical benefits and uses. For example:
– Saving time by automating repetitive tasks in excel, e.g. formatting, or reorganising data.
– Potentially getting better data insights because Python enables the handling of large data sets and can be more efficient in processing and analysing that data.
– Saving time and doing a better job of data cleaning, e.g. Python is better at locating missing values, standardising formats, removing duplicates, and using techniques like regular expressions for pattern-based transformations.
– Improved data analysis and analytics due to the use of Python’s powerful data analysis libraries, e.g. Pandas, Matplotlib, and scikit-learn and the fact that Python in Excel leverages Anaconda (a popular enterprise repository) Distribution for Python running in Azure. This can help with complex calculations, statistical analysis, and data transformations that might be cumbersome or inefficient in Excel.
– Advanced visualisation. I.e., Python charting libraries like Matplotlib and seaborn enabling the creation of a wide variety of charts, spanning from conventional bar graphs and line plots to more specialized visualisations such as heatmaps, violin plots, and swarm plots.
– Helping to focus collaborative work efforts, e.g. where multiple people or systems are providing data in different formats or structures, Python acts as an aggregator, harmonising and consolidating diverse data sources into a single Excel sheet or structure.
– Python scripts can be scheduled to run at specified intervals, thereby making it easier to update or analyse Excel data even when you’re not around.
– Using Python as a bridge to enable Excel data to interact with other web applications, databases, or other external systems.
– Python scripts can be used to create custom functions not natively available in Excel, thereby expanding the scope of what can be done with Excel.
– Python can be used to periodically back up Excel files and even maintain versions (if needed).
– Python libraries like scikit-learn and statsmodels can be leveraged to apply popular machine learning, predictive analytics, and forecasting techniques, e.g. regression analysis, time series modelling, and more.
Examples
Some everyday examples of how using the power of Python in Excel could help businesses include:
– Making monthly sales reports better as well as faster and easier to produce. For example, if a sales manager needs to compile monthly sales reports and receives sales data from multiple regions in different Excel files, a Python script can be written to automatically consolidate all these files into a master report.
– Helping to track the expenses of a small business by using Python to automatically categorise and summarise expenses from an Excel sheet, thereby helping to track where money is being spent most frequently.
– In retail, a store manager could use a Python script to alert them when inventory for a particular item goes below a certain threshold (based on the data in the Excel inventory list).
– Financial analysts could predict future revenue or costs by using Python apply complex forecasting models on past financial data in Excel.
– In accounts, if a business needs to generate bulk invoices, Python can be used to save time by pulling data from an Excel sheet (like client details and amounts) and produce individual invoice files for each client.
– A business with critical data in Excel can have Python scripts scheduled to automatically back up these files at regular intervals, thereby ensuring data safety.
Other examples of what businesses can use Python scripts in combination with Excel include employee scheduling, e.g. generating shift schedules, quickly analysing any customer feedback collected in Excel, automatically highlighting best prices collected in Excel from different vendors, calculating commission for sales staff from figures collected in Excel, and analysing supplier delivery performance, e.g. delivery date and time records held in Excel.
What Does This Mean For Your Business?
In short, releasing Python in Excel enables businesses (that leverage the integration) to effectively ‘supercharge’ their data processing and analysis capabilities, thereby giving them the ability to handle more complex tasks, larger data sets, and integrate with a broader range of technologies.
This could improve productivity, competitiveness, give new insights and reveal new business opportunities, save time, and produce better quality reports and visualisations which can improve transparency and business decision making. The fact(s) that Python in Excel doesn’t require any setup, integrates seamlessly with Teams, plus works securely in the cloud must surely also be attractive to businesses, many of whom now have remote and flexible working (all Teams users have access and security worries are minimised). Most businesses must, however, wait a little longer to start using the power of Python in Excel because it’s currently only available to users running Beta Channel on Windows and Microsoft 365 Insider Program members, although it will start to roll out with build 16.0.16818.20000, and then to the other platforms at a later date.